/*******************************************************************************
 * This file is part of blueprint4j www.blueprint4j.org.
 * 
 *     blueprint4j is free software: you can redistribute it and/or modify
 *     it under the terms of the GNU Lesser General Public License as published by
 *     the Free Software Foundation, either version 3 of the License, or
 *     (at your option) any later version.
 * 
 *     blueprint4j is distributed in the hope that it will be useful,
 *     but WITHOUT ANY WARRANTY; without even the implied warranty of
 *     MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 *     GNU Lesser General Public License for more details.
 * 
 *     You should have received a copy of the GNU Lesser General Public License
 *     along with blueprint4j. If not, see <http://www.gnu.org/licenses/>.
 ******************************************************************************/
package blueprint4j.db;

import java.io.File;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;

import blueprint4j.utils.Log;
import blueprint4j.utils.Utils;
import blueprint4j.utils.VectorString;

public class DataBaseUtilsMySQL extends DataBaseUtils {

	//public static String PATH_TO_MYSQL = Settings.getString("database.mysql.path","C:/mysql/");;

	/*
	static {
		try {
			if (
				System.getProperty("os.name").indexOf("Windows") != -1 &&
				System.getProperty("jni.disabled")==null &&
				Registry.getAllLocal("SOFTWARE\\MySQL AB") != null
			) {
				String path = Registry.getLocal("SOFTWARE\\MySQL AB\\"+Registry.getAllLocal("SOFTWARE\\MySQL AB").getIndexOfPartial("MySQL Server"),"Location",null);
				if (path != null) PATH_TO_MYSQL = path;
			}
		} catch (Throwable th) {
			Log.debug.out(th);
		}
	}
*/
	//public static final String PATH_TO_MYSQL_DB = PATH_TO_MYSQL+ "/data";

	private java.text.SimpleDateFormat simple_date_format = new java.text.SimpleDateFormat( "yyyy-MM-dd" );
	private java.text.SimpleDateFormat simple_time_format = new java.text.SimpleDateFormat( "HH:mm:ss" );
	private java.text.SimpleDateFormat simple_date_and_time_format = new java.text.SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" );

	/**
	 * This method reads the next index field from the index field table.
	 */
	private final static long ID_NUMBER_GAP=100;
	private static long next_id_number=0;
	private static long next_id_block=0;

	public SimpleDateFormat getDateTimeFormat() {
		return simple_date_and_time_format;
	}

	public SimpleDateFormat getTimeFormat() {
		return simple_time_format;
	}

	public SimpleDateFormat getDateFormat() {
		return simple_date_format;
	}

	public Long getNextIndexField(Entity entity)
	throws SQLException {
		if (next_id_number==next_id_block) {
			Statement statement = DBTools.getLocalConnection(entity.getConnection()).createStatement();
			statement.execute("insert into " + IndexTable.TABLE_NAME + "(tablename) values ('" + entity.getTableName() + "')");
			ResultSet result_set = statement.executeQuery("select LAST_INSERT_ID()");
			if (!result_set.next()) {
				throw new SQLException("Could not read the next inserted index field");
			}
			next_id_number = result_set.getLong(1)*ID_NUMBER_GAP;
			next_id_block=next_id_number+ID_NUMBER_GAP;
			result_set.close();
			statement.close();
		}
		return new Long(next_id_number++);
	}

	public void loadIdAfterInsert(DBConnection connection,FieldId id)
	throws SQLException {
		Statement smt = connection.createStatement();
		try {
			ResultSet rs = smt.executeQuery("select LAST_INSERT_ID()");
			rs.next();
			id.setAsString(rs.getString(1));
		}
		finally {
			smt.close();
		}
	}

	protected void createTableVersion(DBConnection dbcon) throws SQLException {
		if (dbcon.getDatabaseMajorVersion() >=4 && dbcon.getDatabaseMinorVersion() >= 1){
			dbcon.execute("create table " + TableVersion.TABLE_NAME + "( " +
							  "name varchar(250),version bigint"+
							  ") engine = MyISAM;"
							 );
		} else {
			dbcon.execute("create table " + TableVersion.TABLE_NAME + "( " +
							  "name varchar(250),version bigint"+
							  ")"
							 );
		}
		dbcon.commit();
	}

	protected void createTable(DBConnection connection,String create_syntax,Entity entity,Field table_fields[])
	throws SQLException {
		String create_sql = create_syntax + " "+ entity.getTableName() + "( ";
		for (int i =0;i < table_fields.length;i++) {
			if (table_fields[i].isKey()) {
				create_sql += table_fields[i].getName() + " " + getSQLType(table_fields[i]) + " primary key("+table_fields[i].getName()+"),";
			} else {
				create_sql += table_fields[i].getName() + " " + getSQLType(table_fields[i]) + ",";
			}
		}
		create_sql = create_sql.substring(0,create_sql.length()-1);
		if (connection.getDatabaseMajorVersion() >=4 && connection.getDatabaseMinorVersion() >= 1){
			if (entity.isStorageTranscational()) {
				create_sql += ") engine=InnoDB ";
			} else {
				create_sql += ") engine=MyISAM ";
			}
		} else {
			create_sql += ")";
		}
		Log.sql.out("Updating Database",create_sql);
		connection.execute(create_sql);
		connection.commit();
	}

	public void createIndexTable(Statement st)
	throws SQLException {
		st.execute("create table d_index_table(index_field bigint unsigned AUTO_INCREMENT,tablename varchar(64) not null,primary key(index_field))");
	}

	/**
	* Convert a Date to SQL leave out the time
	*
	* @param date the Date to be converted
	* @return a string copy of the date ready to by used in the SQL including ' characters
	*/
	public String convertDateToSQLString(Date date) {
		if (date == null) {
			return "NULL";
		}
		return "'" + simple_date_format.format(date) + "'";
	}

	/**
	* Convert a Time to SQL leave out the date
	*
	* @param date the Date to be converted
	* @return a string copy of the date ready to by used in the SQL including ' characters
	*/
	public String convertTimeToSQLString(Date date) {
		if (date == null) {
			return "NULL";
		}
		return "'" + simple_time_format.format(date) + "'";
	}

	public String castTime(String date) {
		return "cast("+date+" AS TIME)";
	}

	/**
	* The SQL statement to retrieve the current date value
	*
	* @return the sql statement used to retrieve the current date value without time
	*/
	public String currentDateSQLMethod() {
		return "CURRENT_DATE()";
	}

	/**
	* Convert a Date to SQL including time
	*
	* @param date the DateTime to be converted
	* @return a string copy of the date ready to by used in the SQL including ' characters
	*/
	public String convertDateTimeToSQLString(Date date) {
		if (date == null) {
			return "NULL";
		}
		return "'" + simple_date_and_time_format.format(date) + "'";
	}

	/**
	* The SQL statement to retrieve the current date value
	*
	* @return the sql statement used to retrieve the current date value with time
	*/
	public String currentDateTimeSQLMethod() {
		return "CURRENT_TIMESTAMP()";
	}

	/**
	* Get the SQL command for top or first
	*
	* @param number The number of rows you would like back from the select.
	* @return Returns the SQL string command to limit the number of rows returned in a system
	* Note Mysql Can also retrieve the last amount,SELECT * FROM table LIMIT 95,-1; # Retrieve rows 96-last.
	*/
	public String sqlFirstString(String sql, int number) {
		return sql + " LIMIT " + number;
	}

	/**
	* Converts a string value to a suitable value for an SQL statement
	*
	* @param source The value of the string
	* @return A converted string ready for use in SQL
	*/
	public String convertStringForSQL(String source) {
		return convertStringForSQLAsStatic(source);
	}

	private static String convertStringForSQLAsStatic(String source) {
		if (source == null) return null;
		return "'" + convertStringForSQLBeforeQuotes(source) + "'";
	}

	private static String convertStringForSQLBeforeQuotes(String source) {
		if (source == null) return null;
		return
			source
			.replaceAll("'","''")
			.replaceAll("\\\\","\\\\\\\\");
	}

	/**
	* Converts a string after is has been returned from the database
	*
	* @param source The value of the string from the database
	* @return A converted string converted from the SQL syntax
	*/
	public String convertStringFromSQL(String source) {
		if (source == null) return null;
		return source;
	}

	public String dateSUB_HOUR(Date date,int hours) {
		return " date_sub("+convertDateTimeToSQLString(date)+", interval "+hours+" HOUR)";
	}

	public String dateSUB_MIN(Date date,int min) {
		return " date_sub("+convertDateTimeToSQLString(date)+", interval "+min+" MIN)";
	}

	public String dateSUB_SEC(Date date,int sec) {
		return " date_sub("+convertDateTimeToSQLString(date)+", interval "+sec+" SEC)";
	}

	public boolean mayUpdateFromResultSet(DBConnection dbcon) throws SQLException {
		return true;
		//return (dbcon.getDatabaseMajorVersion() >=4 && dbcon.getDatabaseMinorVersion() >= 1);
	}

	public void executeKeepAliveSQL(DBConnection connection)
	throws SQLException {
		Statement smt = connection.createStatement();
		smt.executeQuery("select 1 = 1");
		smt.close();
	}

	public void executeOptimizeTable(DBConnection dbcon,String table_name)
	throws SQLException {
		Statement smt = dbcon.createStatement();
		Log.trace.out("SQL Maintanance","optimize table " + table_name);
		smt.executeQuery("optimize table " + table_name);
		smt.close();
	}

	public void dropIndex(DBConnection connection,String table_name,String index_name)
	throws SQLException,DataException {
		Statement smt = connection.createStatement();
		if (new VectorString(smt.executeQuery("show index from " + table_name),"Key_name").contains(index_name)) {
			Log.trace.out("DATABASE UPDATE INDEX","DROP INDEX ["+index_name+"] ON TABLE ["+table_name+"]");
			connection.execute("drop index " + index_name + " on " + table_name);
		}
		smt.close();
	}

	public String getSQLType(FieldAutoId field){return "bigint AUTO_INCREMENT not null,primary key("+field.getName()+")";}

	public String getSQLType(FieldBigDecimal field) {
		return "double";
	}
	public String getSQLType(FieldBigInteger field) {
		return "bigint";
	}
	public String getSQLType(FieldBoolean field) {
		return "char(1)";
	}
	public String getSQLType(FieldCharacter field) {
		return "char(1)";
	}
	public String getSQLType(FieldColor field) {
		return "char(6)";
	}
	public String getSQLType(FieldCurrency field) {
		return "decimal(10,2)";
	}
	public String getSQLType(FieldDate field) {
		return "date";
	}
	public String getSQLType(FieldDateTime field) {
		return "datetime";
	}
	public String getSQLType(FieldDouble field) {
		return "double";
	}
	public String getSQLType(FieldInteger field) {
		return "int";
	}
	public String getSQLType(FieldLong field) {
		return "bigint";
	}
	public String getSQLType(FieldString field) {
		return "varchar("+field.getLength()+")";
	}
	public String getSQLType(FieldTime field) {
		return "time";
	}
	public String getSQLType(FieldText field) {
		return "text";
	}
	public String getSQLType(FieldImage field) {
		return "longblob";
	}
	public String getSQLType(FieldProperties field) {
		return "text";
	}
	public String getSQLType(FieldBlob field) {
		return "longblob";
	}

	public void addField(DBConnection dbcon,Field field)
	throws SQLException {
		if (field.isKey()) {
			Log.trace.out("Updating Database","alter table " + field.getEntity().getTableName() + " add column " + field.getName() + " " + getSQLType(field) + " not null");
			dbcon.execute("alter table " + field.getEntity().getTableName() + " add column " + field.getName() + " bigint AUTO_INCREMENT not null,ADD PRIMARY KEY ("+field.getName()+")");
		} else {
			if (!field.mayFieldBeNull()) {
				Log.trace.out("Updating Database","alter table " + field.getEntity().getTableName() + " add column " + field.getName() + " " + getSQLType(field) + " not null");
				dbcon.execute("alter table " + field.getEntity().getTableName() + " add column " + field.getName() + " " + getSQLType(field) + " not null");
			} else {
				Log.trace.out("Updating Database","alter table " + field.getEntity().getTableName() + " add column " + field.getName() + " " + getSQLType(field));
				dbcon.execute("alter table " + field.getEntity().getTableName() + " add column " + field.getName() + " " + getSQLType(field));
			}
		}
	}

	public void modifyTableField(DBConnection connection,String old_name,Field field)
	throws SQLException {
		if (!field.mayFieldBeNull()) {
			Log.trace.out("Updating Database","alter table " + field.getEntity().getTableName() + " change column " + old_name + " " + field.getName() + " " + getSQLType(field) +" not null");
			connection.execute("alter table " + field.getEntity().getTableName() + " change column " + old_name + " " + field.getName() + " " + getSQLType(field) +" not null");
		} else {
			Log.trace.out("Updating Database","alter table " + field.getEntity().getTableName() + " change column " + old_name + " " + field.getName() + " " + getSQLType(field));
			connection.execute("alter table " + field.getEntity().getTableName() + " change column " + old_name + " " + field.getName() + " " + getSQLType(field));
		}
	}

	public void modifyTableField(DBConnection connection,Field field)
	throws SQLException {
		modifyTableField(connection,field.getName(),field);
	}

	public String getDropField(String name,String tablename) {
		return "alter table " + tablename + " drop column " + name;
	}

	public void setupDatabaseServer(DBConnection connect)
	throws SQLException {
		// TURN OFF CONNECTION TIMEOUT FOR MYSQL
		Log.trace.out("Database Setup","Init Database");
//		connect.execute("set GLOBAL wait_timeout = -1");
//		connect.execute("set GLOBAL interactive_timeout=-1");
//		connect.execute("set GLOBAL max_allowed_packet = 15242880");
	}

	/**
	 * This will backup and zip the database
	 */
	public void backupDB(DBConnection dbcon,File dbfile)
	throws Throwable {/*
		Statement smt = dbcon.createStatement();
		try {
			VectorString tables = dbcon.getTables();
			for (int i =0;i < tables.size();i++) {
				smt.execute("LOCK TABLE "+tables.get(i)+" READ, "+tables.get(i)+" AS lock_"+tables.get(i)+" READ");
			}
			smt.execute("flush tables");
			File dbdir = new File(PATH_TO_MYSQL_DB+"/"+dbcon.getDatabaseName());
			File list[] = dbdir.listFiles();
			ZipOutputStream zipstream = new ZipOutputStream(new FileOutputStream(dbfile));
			for (int i = 0;i < list.length;i++) {
				ZipEntry entry = new ZipEntry(list[i].getName());
				zipstream.putNextEntry(entry);
				Utils.readWrite(new FileInputStream(list[i]),zipstream,false);
			}
			zipstream.finish();
			zipstream.close();
			smt.execute("unlock tables");
		} finally {
			smt.close();
		}*/
	}

	/**
	* In order to do this the server must be stopped
	*/
	public void restoreDB(DBConnection dbcon,File dbfile)
	throws Throwable {
		/*
		if (!dbfile.exists()) {
			throw new DataException("Can not restore from an database that does not exist ["+dbfile.getAbsolutePath()+"]");
		}
		// DELETE ALL FILES
		Utils.deleteDirectory(new File(PATH_TO_MYSQL_DB+"/"+dbcon.getDatabaseName()));
		ZipFile zipfile = new ZipFile(dbfile);
		java.util.Enumeration entries = zipfile.entries();
		if (entries.hasMoreElements()) {
			ZipEntry entry = null;
			do {
				entry =(ZipEntry)entries.nextElement();
				File tbfile = new File(PATH_TO_MYSQL_DB+"/"+dbcon.getDatabaseName()+"/"+entry.getName());
				Utils.readWrite(zipfile.getInputStream(entry),new FileOutputStream(tbfile));
			} while (entries.hasMoreElements());
		}*/
	}

	public boolean createDatabase(String path,String dbname,String username,String password)  {
/*		try {
			Process p = null;
			// GET THE INSTALLATION PATH
			if (Utils.isWindows()){
				File dbfile = new File(PATH_TO_MYSQL_DB + "/"+dbname);
				if (dbfile.exists()) {
					return true;
				}
				Log.trace.out("Creating database " + PATH_TO_MYSQL_DB + "/"+dbname);
				if (!dbfile.mkdir()) {
					Log.debug.out("Failed to create database ["+dbname+"] on path ["+dbfile.getAbsolutePath()+"]");
					return false;
				}
				return true;
			} else {
				return true;
			}
		} catch (Exception exception) {
			exception.printStackTrace();
			Log.debug.out(exception);
			return false;
		}*/
		return false;
	}

	public void bulkLoadFile(DBConnection dbcon,String tablename,File file,String delimiter)
	throws SQLException {
		dbcon.execute(
			"LOAD DATA INFILE '"+Utils.replaceAll(file.getAbsolutePath(),"\\","/")+"' " +
			"INTO TABLE "+tablename+" " +
			"FIELDS TERMINATED BY '"+ delimiter +"'"
		);
	}

	public void exportData(DBConnection dbcon,String select,File bulkfile)
	throws SQLException {
		dbcon.setAutoCommit(true);
		Statement smt = dbcon.createStatement();
		try {
			File tfile = File.createTempFile("mysql","bulkexport");
			tfile.delete();
			String tpath = tfile.getAbsolutePath();
			tpath = Utils.replaceAll(tpath,"\\","/");
			Log.trace.out("BULK EXPORT",select + " INTO OUTFILE '" + tpath+"' FIELDS TERMINATED BY ','");
			smt.execute(select + " INTO OUTFILE '" + tpath+"' FIELDS TERMINATED BY ','");
			// NOT CONVERT \N TO EMPTY FIELDS
			java.io.FileOutputStream fout = new java.io.FileOutputStream(bulkfile);
			java.io.LineNumberReader reader = new java.io.LineNumberReader(
															 new java.io.InputStreamReader(
																 new java.io.FileInputStream(tfile)
															 )
														 );
			for (String line = reader.readLine();line != null;line = reader.readLine()) {
				line = Utils.replaceAll(line,"\\N","");
				fout.write((line+"\n").getBytes());
			}
			tfile.delete();
			fout.close();
		} catch (Exception ex) {
			throw new DataException(ex);
		} finally {
			smt.close();
		}
	}
}
